import pool from '../config/db.js';
import { productCache, bannerCache } from '../services/cacheService.js';

// 使用 Map 作为内存缓存，避免额外依赖
const cache = new Map();
const CACHE_DURATION = 5 * 60 * 1000; // 5分钟缓存

class Product {
  static async getHotProducts(limit = 8) {
    try {
      console.log('Getting hot products with limit:', limit);

      // 简化查询，确保返回基本数据
      const [rows] = await pool.query(`
        SELECT 
          p.id,
          p.name,
          p.description,
          p.price,
          p.image,
          p.sales,
          p.stock,
          COALESCE(AVG(r.rating), 0) as avg_rating,
          COUNT(DISTINCT r.id) as review_count
        FROM products p
        LEFT JOIN reviews r ON p.id = r.product_id
        WHERE p.is_hot = true
        GROUP BY p.id, p.name, p.description, p.price, p.image, p.sales, p.stock
        ORDER BY p.sales DESC
        LIMIT ?
      `, [Number(limit)]);

      console.log('Raw database result:', rows);

      // 确保返回的是数组
      if (!Array.isArray(rows)) {
        console.error('Query result is not an array:', rows);
        return [];
      }

      // 处理数据
      const processedProducts = rows.map(product => ({
        id: product.id,
        name: product.name || '',
        description: product.description || '',
        price: Number(product.price || 0),
        image: product.image || '',
        sales: Number(product.sales || 0),
        stock: Number(product.stock || 0),
        avg_rating: Number(product.avg_rating || 0).toFixed(1),
        review_count: Number(product.review_count || 0)
      }));

      console.log('Processed products:', processedProducts);
      return processedProducts;
    } catch (error) {
      console.error('Error in getHotProducts:', error);
      return []; // 出错时返回空数组
    }
  }

  static async getBanners() {
    try {
      const cached = await bannerCache.get('active');
      if (cached) return cached;

      const [rows] = await pool.query(`
        SELECT * FROM banners 
        WHERE is_active = true 
        ORDER BY sort_order
      `);

      // 处理图片路径，移除重复的 /uploads/
      const banners = rows.map(banner => ({
        ...banner,
        image: banner.image && !banner.image.startsWith('http')
          ? banner.image.replace(/^\/uploads\//, '') // 移除开头的 /uploads/
          : banner.image
      }));

      await bannerCache.set('active', banners);
      return banners;
    } catch (error) {
      console.error('Error in getBanners:', error);
      return [];
    }
  }

  // 清除缓存的方法
  static async clearCache(pattern) {
    await productCache.clear(pattern);
  }

  static async getThemeProducts(themeId, limit = 4) {
    try {
      const [rows] = await pool.query(`
        SELECT 
          p.*,
          t.name as theme_name,
          t.description as theme_description,
          COALESCE(AVG(r.rating), 0) as avg_rating,
          COUNT(DISTINCT r.id) as review_count
        FROM products p
        JOIN themes t ON p.theme_id = t.id
        LEFT JOIN reviews r ON p.id = r.product_id
        WHERE t.id = ? AND t.is_active = true
        GROUP BY p.id
        ORDER BY p.created_at DESC
        LIMIT ?
      `, [themeId, limit]);

      return rows.map(product => ({
        ...product,
        price: Number(product.price),
        avg_rating: Number(product.avg_rating).toFixed(1),
        review_count: Number(product.review_count),
        image: product.image && !product.image.startsWith('http')
          ? `/uploads/${product.image}`
          : product.image
      }));
    } catch (error) {
      console.error('Error in getThemeProducts:', error);
      return [];
    }
  }

  static async getActiveTheme() {
    try {
      const [rows] = await pool.query(`
        SELECT * FROM themes 
        WHERE is_active = true 
        ORDER BY created_at DESC 
        LIMIT 1
      `);
      return rows[0] || null;
    } catch (error) {
      console.error('Error in getActiveTheme:', error);
      return null;
    }
  }

  static async getProductById(id) {
    try {
      const [rows] = await pool.query(
        `SELECT p.*, t.name as theme_name, t.description as theme_description 
         FROM products p 
         LEFT JOIN themes t ON p.theme_id = t.id 
         WHERE p.id = ?`,
        [id]
      );
      return rows[0];
    } catch (error) {
      console.error('Error getting product:', error);
      throw error;
    }
  }

  static async generateStory(productName, description) {
    try {
      // 这里可以接入 AI 接口，暂时返回模拟数据
      return {
        title: `${productName}的故事`,
        content: `这是一个关于${productName}的温馨故事。${description}`
      };
    } catch (error) {
      console.error('Error generating story:', error);
      throw error;
    }
  }

  static async getProductReviews(productId) {
    try {
      const [rows] = await pool.query(
        `SELECT 
          r.*,
          u.username,
          u.email,
          COUNT(*) OVER() as total_count,
          AVG(r.rating) OVER() as average_rating
         FROM reviews r 
         JOIN users u ON r.user_id = u.id 
         WHERE r.product_id = ? 
         ORDER BY r.created_at DESC`,
        [productId]
      );
      
      // 处理评论统计数据
      const stats = rows.length > 0 ? {
        totalCount: rows[0].total_count,
        averageRating: Number(rows[0].average_rating).toFixed(1)
      } : {
        totalCount: 0,
        averageRating: 0
      };
      
      // 移除统计字段
      const reviews = rows.map(row => {
        const { total_count, average_rating, ...review } = row;
        return review;
      });
      
      return {
        stats,
        reviews
      };
    } catch (error) {
      console.error('Error getting product reviews:', error);
      throw error;
    }
  }

  static async createReview(productId, userId, data) {
    try {
      const [result] = await pool.query(
        `INSERT INTO reviews (product_id, user_id, title, content, rating) 
         VALUES (?, ?, ?, ?, ?)`,
        [productId, userId, data.title, data.content, data.rating]
      );
      return result.insertId;
    } catch (error) {
      console.error('Error creating review:', error);
      throw error;
    }
  }

  static async getNewProducts({ page, limit, sort, minPrice, maxPrice, categories }) {
    try {
      // 构建基础查询
      let query = `
        SELECT 
          p.*,
          np.start_time as new_start_time,
          np.end_time as new_end_time,
          COUNT(*) OVER() as total_count
        FROM products p
        INNER JOIN new_products np ON p.id = np.product_id
        WHERE 1=1
      `;
      
      const params = [];
      
      // 添加价格筛选
      if (minPrice !== null) {
        query += ' AND p.price >= ?';
        params.push(minPrice);
      }
      
      if (maxPrice !== null) {
        query += ' AND p.price <= ?';
        params.push(maxPrice);
      }

      // 添加分类筛选
      if (categories && categories.length > 0) {
        query += ' AND p.category IN (?)';
        params.push(categories);
      }
      
      // 添加排序
      switch (sort) {
        case 'price_asc':
          query += ' ORDER BY p.price ASC';
          break;
        case 'price_desc':
          query += ' ORDER BY p.price DESC';
          break;
        case 'newest':
        default:
          query += ' ORDER BY np.start_time DESC';
      }
      
      // 添加分页
      query += ' LIMIT ? OFFSET ?';
      params.push(limit, (page - 1) * limit);

      console.log('Executing query:', query);
      console.log('With params:', params);
      
      const [rows] = await pool.query(query, params);
      
      console.log('Query result:', rows);

      return {
        products: rows.map(row => {
          const { total_count, new_start_time, new_end_time, ...product } = row;
          return {
            ...product,
            price: parseFloat(product.price),
            image: product.image.startsWith('http') 
              ? product.image 
              : `/uploads/${product.image}`,
            isNew: true,
            newStartTime: new_start_time,
            newEndTime: new_end_time
          };
        }),
        total: rows.length > 0 ? rows[0].total_count : 0
      };
    } catch (error) {
      console.error('Error in getNewProducts:', error);
      throw error;
    }
  }

  static async getHotProducts({ limit = 8, minPrice = null, maxPrice = null } = {}) {
    const cacheKey = `hot_${limit}_${minPrice || 'min'}_${maxPrice || 'max'}`;
    
    try {
      const cached = await productCache.get(cacheKey);
      if (cached) return cached;

      let sql = `
        SELECT 
          p.*,
          COALESCE(AVG(r.rating), 0) as avg_rating,
          COUNT(DISTINCT r.id) as review_count
        FROM products p
        LEFT JOIN reviews r ON p.id = r.product_id
        WHERE p.is_hot = 1
      `;

      const params = [];

      if (minPrice !== null && maxPrice !== null) {
        sql += ` AND p.price BETWEEN ? AND ?`;
        params.push(minPrice, maxPrice);
      } else if (minPrice !== null) {
        sql += ` AND p.price >= ?`;
        params.push(minPrice);
      } else if (maxPrice !== null) {
        sql += ` AND p.price <= ?`;
        params.push(maxPrice);
      }

      sql += `
        GROUP BY p.id 
        ORDER BY avg_rating DESC 
        LIMIT ?
      `;
      params.push(limit);

      const [rows] = await pool.query(sql, params);

      const processedProducts = rows.map(product => ({
        ...product,
        image: product.image && !product.image.startsWith('http') 
          ? `/uploads/${product.image}`
          : product.image,
        avg_rating: Number(product.avg_rating).toFixed(1)
      }));

      await productCache.set(cacheKey, processedProducts);
      return processedProducts;
    } catch (error) {
      console.error('Error in getHotProducts:', error);
      return [];
    }
  }
}

export default Product; 